SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE Function [dbo].[InAmount](@DrugId nvarchar(50),@Style varchar(1),@fromdate datetime,@todate datetime)
Returns @InAmount table
(
	Quantity int,
	Amount decimal
)
as
Begin
	if(@style='')
		begin
		Insert @InAmount select  sum(quantity) as Quantity ,Sum(Quantity*UnitPrice) as TotalAmount
		from GRNDETAILS AS GD INNER JOIN GRN G ON G.GrnId=GD.GrnId 
		where GrnDate between @fromdate and @todate and Drugid=@DrugId and G.IsComplete=1
		group by DrugId
		end
	else
		begin
		Insert @InAmount select  sum(quantity) as Quantity,Sum(Quantity*UnitPrice) as TotalAmount
		from GRNDETAILS AS GD INNER JOIN GRN G ON G.GrnId=GD.GrnId 
		where GrnDate between @fromdate and @todate and Drugid=@DrugId and G.IsComplete=1
		AND Left(GD.GrnId,1)= @Style
		group by DrugId
		end

	
return
End
GO
